﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;

/// <summary>
/// Summary description for DAL_PhieuKham
/// </summary>
public class DAL_PhieuKham : SqlConnectionRun
{
    public DAL_PhieuKham()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public DataTable TraCuuSoPhieuKham(string MaPhieu, string MaBN, string HoTenBN, string TenBacSi, string MaKhoa, string XuTri, string TuNgay, string DenNgay)
    {
        //int beginRow = (int.Parse(pageIndex) - 1) * 10;
        //int endRow = beginRow + 10;
        string sqlselect = @"SELECT * FROM (" +
                     @" Select ROW_NUMBER() OVER(ORDER BY s.Ngay) RN, RTRIM(s.MaPK) as MaPK, Convert(nvarchar(50),s.Ngay,103)+' '+Convert(nvarchar(50),s.Ngay,108) as [Ngay],
                    s.XuTri,s.MaBN,IsNull(btn.HoTen,'') As HoTen,Case IsNull(btn.GioiTinh,'') when 1 then 'Nam' when 0 then N'Nữ' else '' end  as GioiTinh ,
                    IsNull(btn.NamSinh,'') As NamSinh, s.TrieuChung, s.ChanDoanSoBo, 
                    s.ChanDoan , s.ICDChanDoan, s.ICDChanDoanPhu1, s.ChanDoanPhu1, s.ICDChanDoanPhu2, s.ChanDoanPhu2, 
                    s.LoiDanBS ,s.HenTaiKham,s.MaBS, s.BacSiKham, s.MaDotKham,s.loai_kham_cp,hc.hoten as tenbs, s.DaKeToa, s.MaKhoa,
                    s.ChiDinhDieuTri, btn.SoBHYT, CONVERT(DATE, btn.HanBH) as HanBH, btn.DoiTuong, ck.ketthuc, s.mapk_chinh
                    from PhongKham..So_PhieuKham  as s 
                    left outer join NghiepVuY..BangTiepNhan as btn on s.mabn=btn.mabn 
                    left join hanhchanhbv..nguoidung hc on hc.manv=s.mabs 
                    left join phongkham..ds_chokham ck on ck.MaDotKham = s.MaDotKham 
                    where (s.loai_kham_cp='khamchinh' or s.loai_kham_cp='themtoa' or s.loai_kham_cp='cls' or s.loai_kham_cp='khamthem' or s.loai_kham_CP='suatoa')";
        string condition = "";
        if (MaPhieu != "")
            condition += " AND s.MaPK LIKE '%" + MaPhieu + "%'";
        if (MaBN != "")
            condition += " AND s.MaBN LIKE '%" + MaBN + "%'";
        if (HoTenBN != "")
            condition += " AND btn.HoTen LIKE N'%" + HoTenBN + "%'";
        if (TenBacSi != "")
            condition += " AND (s.BacSiKham LIKE N'%" + TenBacSi + "%' OR s.MaBS LIKE '%" + TenBacSi + "%')";
        if (MaKhoa != "")
            condition += " AND s.MaKhoa = '" + MaKhoa + "'";
        if (XuTri != "" && XuTri != "tatca")
        {
            switch (XuTri)
            {
                case "chua":
                    condition += " AND s.XuTri = '0'";
                    break;
                default:
                    condition += " AND s.XuTri = '" + XuTri + "'";
                    break;
            }
        }
        if (TuNgay != "" && DenNgay != "")
            condition += " AND CONVERT(DATE, s.Ngay,103) BETWEEN CONVERT(DATE, '" + TuNgay + "', 103) AND CONVERT(DATE, '" + DenNgay + "', 103)";

        if (condition != "")
            sqlselect += condition;
        sqlselect += ") a order by Ngay desc";
        DataTable dt = SqlExecuteQueryReader(sqlselect);
        return dt;
    }

    public DataTable LayThongTinPhieuKham(string MaPhieu)
    {
        string store = "PhongKham..LayThongTin_PK";
        List<SqlParameter> parameters = new List<SqlParameter>();
        parameters.Add(new SqlParameter("@MaPK", MaPhieu));
        DataTable dt = SqlRunStoredProcedure(store, parameters);
        return dt;
    }

    public DataTable LayChiTietPhieuKham(string MaPhieu, string DaKeToa)
    {
        string sql = "";
        if (DaKeToa == "True")
        {
            sql = "Select * from ChiTiet_PhieuKham where MaPK='" + MaPhieu + "' order by ID ";
        }
        if (DaKeToa == "False")
        {
            sql = "select ct.* from so_phieukham pk left join chitiet_phieukham ct on pk.mapk=ct.mapk where pk.mapk_chinh='" + MaPhieu + "'";
        }        
        DataTable dt = SqlExecuteQueryReader(sql);
        return dt;
    }

    /// <summary>
    /// Thêm sổ phiếu khám mới về DB
    /// </summary>
    /// <param name="_sMaPK"></param>
    /// <param name="_sMaKhoa"></param>
    /// <param name="_dtNgay"></param>
    /// <param name="_sMaThuDV"></param>
    /// <param name="_sMaBA"></param>
    /// <param name="_sMaBN"></param>
    /// <param name="_sNguonBN"></param>
    /// <param name="_sTrieuChung"></param>
    /// <param name="_sChanDoanSoBo"></param>
    /// <param name="_sChanDoan"></param>
    /// <param name="_sICDChanDoan"></param>
    /// <param name="_sChanDoanPhu1"></param>
    /// <param name="_sICDChanDoanPhu1"></param>
    /// <param name="_sChanDoanPhu2"></param>
    /// <param name="_sICDChanDoanPhu2"></param>
    /// <param name="_sXuTri"></param>
    /// <param name="_sChiDinhDieuTri"></param>
    /// <param name="_sLoiDanBS"></param>
    /// <param name="_sHenTaiKham"></param>
    /// <param name="_dtNgayTaiKham"></param>
    /// <param name="_sMaBS"></param>
    /// <param name="_sBacSiKham"></param>
    /// <param name="_sChuyenDenKhoa"></param>
    /// <param name="_sChuyenDenBV"></param>
    /// <param name="_bDaKeToa"></param>
    /// <param name="_sDBName"></param>
    /// <param name="_sMaDotKham"></param>
    /// <param name="_sLoaiKham_CP"></param>
    /// <param name="_sMaPK_Chinh"></param>
    /// <param name="_sKetThucKham"></param>
    /// <returns>0: nếu OK</returns>
    public bool addPhieuKham(string _sMaPK, string _sMaKhoa, DateTime _dtNgay, string _sMaThuDV, string _sMaBA, string _sMaBN, string _sNguonBN,
        string _sTrieuChung, string _sChanDoanSoBo, string _sChanDoan, string _sICDChanDoan, string _sChanDoanPhu1, string _sICDChanDoanPhu1,
        string _sChanDoanPhu2, string _sICDChanDoanPhu2, string _sXuTri, string _sChiDinhDieuTri, string _sLoiDanBS, string _sHenTaiKham,
        DateTime _dtNgayTaiKham, string _sMaBS, string _sBacSiKham, string _sChuyenDenKhoa, string _sChuyenDenBV, bool _bDaKeToa,
        string _sDBName, string _sMaDotKham, string _sLoaiKham_CP, string _sMaPK_Chinh, string _sKetThucKham)
    {
        //string sql = "PhongKham..usp_ToaThuoc_SoPhieuKham_INSERT"; //Không hoạt động được
        string sql = "PhongKham..proc_AddNewSoPhieuKham";
        List<SqlParameter> parameters = new List<SqlParameter>();
        parameters.Add(new SqlParameter("@MaPK", _sMaPK));
        parameters.Add(new SqlParameter("@MaKhoa", _sMaKhoa));
        parameters.Add(new SqlParameter("@Ngay", _dtNgay));
        parameters.Add(new SqlParameter("@MaThuDV", _sMaThuDV));
        parameters.Add(new SqlParameter("@MaBA", _sMaBA));
        parameters.Add(new SqlParameter("@MaBN", _sMaBN));
        parameters.Add(new SqlParameter("@NguonBN", _sNguonBN));
        parameters.Add(new SqlParameter("@TrieuChung", _sTrieuChung.Trim()));
        parameters.Add(new SqlParameter("@ChanDoanSoBo", _sChanDoanSoBo.Trim()));
        parameters.Add(new SqlParameter("@ChanDoan", _sChanDoan));
        parameters.Add(new SqlParameter("@ICDChanDoan", _sICDChanDoan.Trim()));
        parameters.Add(new SqlParameter("@ChanDoanPhu1", _sChanDoanPhu1.Trim()));
        parameters.Add(new SqlParameter("@ICDChanDoanPhu1", _sICDChanDoanPhu1));
        parameters.Add(new SqlParameter("@ChanDoanPhu2", _sChanDoanPhu2.Trim()));
        parameters.Add(new SqlParameter("@ICDChanDoanPhu2", _sICDChanDoanPhu2));
        parameters.Add(new SqlParameter("@XuTri", _sXuTri));
        parameters.Add(new SqlParameter("@ChiDinhDieuTri", _sChiDinhDieuTri.Trim()));
        parameters.Add(new SqlParameter("@LoiDanBS", _sLoiDanBS.Trim()));
        parameters.Add(new SqlParameter("@HenTaiKham", _sHenTaiKham));
        parameters.Add(new SqlParameter("@NgayTaiKham", _dtNgayTaiKham));
        parameters.Add(new SqlParameter("@MaBS", _sMaBS));
        parameters.Add(new SqlParameter("@BacSiKham", _sBacSiKham));
        parameters.Add(new SqlParameter("@ChuyenDenKhoa", _sChuyenDenKhoa));
        parameters.Add(new SqlParameter("@ChuyenDenBV", _sChuyenDenBV));
        parameters.Add(new SqlParameter("@daketoa", _bDaKeToa));
        parameters.Add(new SqlParameter("@DBName", _sDBName));
        parameters.Add(new SqlParameter("@MaDotKham", _sMaDotKham));
        parameters.Add(new SqlParameter("@loai_kham_CP", _sLoaiKham_CP));
        parameters.Add(new SqlParameter("@mapk_chinh", _sMaPK_Chinh));
        parameters.Add(new SqlParameter("@ketthuckham", _sKetThucKham));
        int result = SqlRunStoredProcedureNoneQuery(sql, parameters);
        return result > 0;
    }

    /// <summary>
    /// Xóa sổ phiếu khám
    /// </summary>
    /// <param name="sMaPhieuKham"></param>
    public bool deleteSo_PhieuKham(string sMaPhieuKham)
    {
        string strDelete = "DELETE FROM PhongKham..SO_PHIEUKHAM WHERE MAPK='" + sMaPhieuKham.Trim() + "'";
        int i = SqlExecuteNoneQuery(strDelete);
        return i > 0;
    }

    /// <summary>
    /// Update sổ phiếu khám
    /// </summary>
    /// <param name="_sMaPK"></param>
    /// <param name="_sMaKhoa"></param>
    /// <param name="_dtNgay"></param>
    /// <param name="_sMaThuDV"></param>
    /// <param name="_sMaBA"></param>
    /// <param name="_sMaBN"></param>
    /// <param name="_sNguonBN"></param>
    /// <param name="_sTrieuChung"></param>
    /// <param name="_sChanDoanSoBo"></param>
    /// <param name="_sChanDoan"></param>
    /// <param name="_sICDChanDoan"></param>
    /// <param name="_sChanDoanPhu1"></param>
    /// <param name="_sICDChanDoanPhu1"></param>
    /// <param name="_sChanDoanPhu2"></param>
    /// <param name="_sICDChanDoanPhu2"></param>
    /// <param name="_sXuTri"></param>
    /// <param name="_sChiDinhDieuTri"></param>
    /// <param name="_sLoiDanBS"></param>
    /// <param name="_sHenTaiKham"></param>
    /// <param name="_dtNgayTaiKham"></param>
    /// <param name="_sMaBS"></param>
    /// <param name="_sBacSiKham"></param>
    /// <param name="_sChuyenDenKhoa"></param>
    /// <param name="_sChuyenDenBV"></param>
    /// <param name="_bDaKeToa"></param>
    /// <param name="_sDBName"></param>
    /// <param name="_sMaDotKham"></param>
    /// <param name="_sLoaiKham_CP"></param>
    /// <param name="_sMaPK_Chinh"></param>
    /// <param name="_sKetThucKham"></param>
    /// <returns></returns>
    public bool updatePhieuKham(string _sMaPK, string _sMaKhoa, DateTime _dtNgay, string _sMaThuDV, string _sMaBA, string _sMaBN, string _sNguonBN,
        string _sTrieuChung, string _sChanDoanSoBo, string _sChanDoan, string _sICDChanDoan, string _sChanDoanPhu1, string _sICDChanDoanPhu1,
        string _sChanDoanPhu2, string _sICDChanDoanPhu2, string _sXuTri, string _sChiDinhDieuTri, string _sLoiDanBS, string _sHenTaiKham,
        DateTime _dtNgayTaiKham, string _sMaBS, string _sBacSiKham, string _sChuyenDenKhoa, string _sChuyenDenBV, bool _bDaKeToa,
        string _sDBName, string _sMaDotKham, string _sLoaiKham_CP, string _sMaPK_Chinh, string _sKetThucKham)
    {

        string sql = "PhongKham..usp_ToaThuoc_SoPhieuKham_UPDATE";
        List<SqlParameter> parameters = new List<SqlParameter>();
        parameters.Add(new SqlParameter("@MaPK", _sMaPK));
        parameters.Add(new SqlParameter("@MaKhoa", _sMaKhoa));
        parameters.Add(new SqlParameter("@Ngay", _dtNgay));
        parameters.Add(new SqlParameter("@MaThuDV", _sMaThuDV));
        parameters.Add(new SqlParameter("@MaBA", _sMaBA));
        parameters.Add(new SqlParameter("@MaBN", _sMaBN));
        parameters.Add(new SqlParameter("@NguonBN", _sNguonBN));
        parameters.Add(new SqlParameter("@TrieuChung", _sTrieuChung));
        parameters.Add(new SqlParameter("@ChanDoanSoBo", _sChanDoanSoBo));
        parameters.Add(new SqlParameter("@ChanDoan", _sChanDoan));
        parameters.Add(new SqlParameter("@ICDChanDoan", _sICDChanDoan));
        parameters.Add(new SqlParameter("@ChanDoanPhu1", _sChanDoanPhu1));
        parameters.Add(new SqlParameter("@ICDChanDoanPhu1", _sICDChanDoanPhu1));
        parameters.Add(new SqlParameter("@ChanDoanPhu2", _sChanDoanPhu2));
        parameters.Add(new SqlParameter("@ICDChanDoanPhu2", _sICDChanDoanPhu2));
        parameters.Add(new SqlParameter("@XuTri", _sXuTri));
        parameters.Add(new SqlParameter("@ChiDinhDieuTri", _sChiDinhDieuTri));
        parameters.Add(new SqlParameter("@LoiDanBS", _sLoiDanBS));
        parameters.Add(new SqlParameter("@HenTaiKham", _sHenTaiKham));
        parameters.Add(new SqlParameter("@NgayTaiKham", _dtNgayTaiKham));
        parameters.Add(new SqlParameter("@MaBS", _sMaBS));
        parameters.Add(new SqlParameter("@BacSiKham", _sBacSiKham));
        parameters.Add(new SqlParameter("@ChuyenDenKhoa", _sChuyenDenKhoa));
        parameters.Add(new SqlParameter("@ChuyenDenBV", _sChuyenDenBV));
        parameters.Add(new SqlParameter("@daketoa", _bDaKeToa));
        parameters.Add(new SqlParameter("@DBName", _sDBName));
        parameters.Add(new SqlParameter("@MaDotKham", _sMaDotKham));
        parameters.Add(new SqlParameter("@loai_kham_CP", _sLoaiKham_CP));
        parameters.Add(new SqlParameter("@mapk_chinh", _sMaPK_Chinh));
        parameters.Add(new SqlParameter("@ketthuckham", _sKetThucKham));
        int result = SqlRunStoredProcedureNoneQuery(sql, parameters);
        return result > 0;
    }

    public string KTra_donthuocdaban(string _sMaPK)
    {

        string store = "PhongKham..KTra_donthuocdaban";
        List<SqlParameter> parameters = new List<SqlParameter>();
        parameters.Add(new SqlParameter("@MaPK", _sMaPK));
        string Toadaban = "";
        string Toadaban_ch = "";
        try
        {
            DataTable dt = SqlRunStoredProcedure(store, parameters);
            foreach (DataRow row in dt.Rows)
            {
                Toadaban = row["toa"].ToString();
                Toadaban_ch = row["SqlCon_Chinh"].ToString();

            }
        }
        catch (Exception)
        {

        }
        string nRetval = "";
        if (Toadaban != "")
        {
            //Toa da ban không the cap nhat
            nRetval = "<p style='margin-top:2px;margin-bottom:2px;background-color:royalblue'><font color=white > &nbsp;Phiếu này không thể cập nhật. Đơn thuốc đã bán!</font></p>";
        }
        if ((Toadaban == "") && (Toadaban_ch != ""))
        {
            //Toa phu da dc toa chinh TH , va toa chinh da ban. không the cap nhat
            nRetval = "<p style='margin-top:2px;margin-bottom:2px;background-color:royalblue'><font color=white > &nbsp;Phiếu này không thể cập nhật. Đơn thuốc đã được Đơn thuốc chính tổng hợp và bán!</font></p>";
        }

        return nRetval;
    }

    public bool checkMaPhieuKhamExists(string _sMaPhieuKham)
    {
        string str = "select count(mapk) from phongkham..so_phieukham where mapk= '" + _sMaPhieuKham.Trim() + "'";
        int nCount = Convert.ToInt32(SqlExecuteQueryScalar(str));
        return nCount > 0;
    }

    public void CapNhatTuDienBacSi(string _mand, string _tenthuoc, string _soluong, string _donvi, string _cachdung, string _lan, string _sl, string _dv, string _ghichu, string _thuocid)
    {
        string sqltudienbs = "select * from PhongKham..TuDienBacSi where TenThuoc=N'" + _tenthuoc + "' and TenBacSi='" + _mand + "'";
        DataTable dt = SqlExecuteQueryReader(sqltudienbs);
        if (dt.Rows.Count > 0)
        {
            foreach (DataRow row in dt.Rows)
            {
                string updatetudien = "UPDATE PhongKham..TuDienBacSi SET SoLuong = N'" + _soluong + "', DonVi = N'" + _donvi + "', CachDung =N'" +
                       _cachdung + "', SoLanDung = N'" + _lan + "', SoLuongDung = N'" + _sl + "', DonViDung = N'" + _dv + "', GhiChu = N'" +
                       _ghichu + "'WHERE (TenThuoc = N'" + _tenthuoc + "') AND (TenBacSi = N'" + _mand + "')  ";
                SqlExecuteNoneQuery(updatetudien);
            }
        }
        else
        {
            string insertTudien = "insert into PhongKham..TuDienBacSi values(N'" + _mand + "',N'" + _tenthuoc + "',N'" + _soluong + "',N'" + _donvi +
           "',N'" + _cachdung + "',N'" + _lan + "',N'" + _sl + "',N'" + _dv + "',N'" + _ghichu + "','" + _thuocid + "')";
            SqlExecuteNoneQuery(insertTudien);
        }
    }

    public void CapNhatDanhSachChoKham(string madotkham, string makhoa)
    {
        string sql = @" update PhongKham..DS_ChoKham set ketoa=0 where Madotkham='" + madotkham + "' and MaKhoa='" + makhoa + "' and DATEDIFF(day,ngay,getdate())=0 ";
        SqlExecuteNoneQuery(sql);
    }

    public string TaoMaPhieu(string tenphieu)
    {       
        string dmyngay = DateTime.Today.Day.ToString() + "/" + DateTime.Today.Month.ToString() + "/" + DateTime.Today.Year.ToString();             
        string ngay = DateTime.Today.ToShortDateString();
        DateTime dngay = DateTime.Today;
        string sqlmaphieu = " Set DATEFORMAT dmy Select * from PhongKham..BangDemMaSo where ten='" + tenphieu + "' and ngay='" + dmyngay + "'";
        DataTable dt = SqlExecuteQueryReader(sqlmaphieu);
        int th;
        string tthang = "";
        int ng;

        string tngay = "";
        string maso = "";
        int dai;
        int tongso = 0;
        if(dt.Rows.Count > 0)
        {
            foreach(DataRow row in dt.Rows)
            {
                tongso = int.Parse(row["TongSo"].ToString()) + 1;
                dai = tongso.ToString().Length;
                dai = 3 - dai;
                for (int i = 1; i <= dai; i++)
                {
                    maso = maso + "0";
                }
            }           
        }
        else
        {
            string sqlinsertmp = "Set DateFormat dmy insert into PhongKham..BangDemMaSo(Ten,Ngay,TongSo) values('" + tenphieu + "','" + dmyngay + "',0)";
            SqlExecuteNoneQuery(sqlinsertmp);
            maso = "00";
            tongso = 1;
        }
        
        th = DateTime.Today.Month.ToString().Length;
        th = 2 - th;
        for (int i = 1; i <= th; i++)
        {
            tthang = tthang + "0";
        }
        tthang = tthang + DateTime.Today.Month.ToString();

        ng = DateTime.Today.Day.ToString().Length;
        ng = 2 - ng;
        for (int i = 1; i <= ng; i++)
        {
            tngay = tngay + "0";
        }
        tngay = tngay + DateTime.Today.Day.ToString();
        //''''''''''''''''
        maso = maso + tongso.ToString();
        string nm = DateTime.Today.Year.ToString();
        nm = nm.Substring(nm.Length - 2);
        tenphieu = tenphieu + nm + tthang + tngay + maso;
        return tenphieu;

    }

    public void TangSoMaPhieu()
    {        
        string dmyngay = DateTime.Today.Day.ToString() + "/" + DateTime.Today.Month.ToString() + "/" + DateTime.Today.Year.ToString();
        string sqlupdatema = " Set DATEFORMAT dmy update PhongKham..BangDemMaSo set tongso=tongso+1 where ten='PK' and ngay='" + dmyngay + "'";
        SqlExecuteNoneQuery(sqlupdatema);
    }

    public void TangMaBenhNhan()
    {      
        string nm = DateTime.Today.Year.ToString();
        nm = nm.Substring(nm.Length - 2);
        string sqlupdatema = "update PhongKham..BangDemMaSo set tongso=tongso+1 where ten='MaBN' and KyHieu='" + nm + "'";
        SqlExecuteNoneQuery(sqlupdatema);
    }

    public bool fnCapNhatKeToaDSChoKham(string madotkham, string makhoa)
    {        
        string strupdate = @"update PhongKham..DS_ChoKham set ketoa=0 where Madotkham='" + madotkham + "' and MaKhoa='" + makhoa + "' and DATEDIFF(day,ngay,getdate())=0 ";
        int i = SqlExecuteNoneQuery(strupdate);
        return i > 0;
    }
}
